<?php
class News
{
	const DBTable = "tbl_news";

	public $News_id;	public $News_md5;	public $Panel_size;	public $Title;	public $Summary;
	public $Description;	public $Date;
	public $Image_thumbnail;	public $Image;	public $Meta_title;	public $Meta_description;	public $Meta_keywords;	public $Show_homepage;
	public $User_id;	public $Status;	public $Is_deleted;	public $Createdate;	public $Updatedate;
	public $Category_array_id;
	public $Url_slug;
	public $Bitly_url;
	private $__mysql;
	private $__nc;

	public function __construct()
	{
		$this->News_id = 0;		$this->News_md5 = null;		$this->Panel_size = 1;		$this->Title = null;		$this->Summary = null;
		$this->Description = null;		$this->Date = date("Y-m-d");
		$this->Image_thumbnail = null;		$this->Image = null;		$this->Meta_title = null;		$this->Meta_description = null;		$this->Meta_keywords = null;		$this->Show_homepage = 0;
		$this->User_id = 0;		$this->Status = 2;		$this->Is_deleted = 0;		$this->Createdate = date("Y-m-d H:i:s");		$this->Updatedate = date("Y-m-d H:i:s");
		$this->Category_array_id = array();
		$this->Url_slug = null;
		$this->Bitly_url = null;
		$this->__mysql = new MYSQL;
		$this->__nc = new News_category;
	}

	public function Save()
	{
		$sqlStr = "insert into ".self::DBTable."
				(panel_size, title, summary, description, date, image_thumbnail, image, meta_title, show_homepage, status, createdate, updatedate, user_id, url_slug)
				values
				(
				'".$this->Panel_size."', 				'".mysql_real_escape_string($this->Title)."', 
				'".mysql_real_escape_string($this->Summary)."', 				'".mysql_real_escape_string($this->Description)."', 				'".$this->Date."', 
				'".mysql_real_escape_string($this->Image_thumbnail)."', 				'".mysql_real_escape_string($this->Image)."', 				'".mysql_real_escape_string($this->Title)."', 				'".$this->Show_homepage."', 				'".$this->Status."', 				'".$this->Createdate."', 
				'".$this->Updatedate."', 
				'".$this->User_id."',
				'".clean_url_accents(mysql_real_escape_string($this->Title))."'
				)";
			$this->__mysql->Exec_query($sqlStr);
			$this->News_id = $this->__mysql->Get_insert_id();

			//create an md5
			$this->News_md5 = substr(md5($this->News_id), 0, 10);
			$this->Quick_update("news_md5", $this->News_md5);

			//insert into tbl_news_category_link
			if(count($this->Category_array_id) > 0)
			{
				foreach($this->Category_array_id as $category_id)
				{
					$sqlStr = "insert into tbl_news_category_link
						(news_id, category_id) 
						values
						(".$this->News_id.", ".$category_id.")
						";
					$this->__mysql->Exec_query($sqlStr);
				}
			}

			$sqlStr = "optimize table ".self::DBTable.", tbl_news_category_link";
			$this->__mysql->Exec_query($sqlStr);

			return $this->News_id;
	}

	public function Update()
	{
		//deal with any assets
		$query = "select image, image_thumbnail from ".self::DBTable." where news_id = '".mysql_real_escape_string($this->News_id)."'";
		$recordset = $this->__mysql->Exec_query($query);
		$result = $this->__mysql->Get_data_assoc($recordset);

		if($this->Image != "" || $this->Image == "delete")
		{
			if($this->Image == "delete")
			{
				$this->Image = null;
				@unlink(ASSET_STORE_NEWS . $result['image']);
			}

			$this->Quick_update("image", $this->Image);
		}

		if($this->Image_thumbnail != "" || $this->Image_thumbnail == "delete")
		{
			if($this->Image_thumbnail == "delete")
			{
				$this->Image_thumbnail = null;
				@unlink(ASSET_STORE_NEWS . $result['image_thumbnail']);
			}

			$this->Quick_update("image_thumbnail", $this->Image_thumbnail);
		}
		//
	
		$sqlStr = "update ".self::DBTable." set
			panel_size = '".$this->Panel_size."', 			title = '".mysql_real_escape_string($this->Title)."', 
			summary = '".mysql_real_escape_string($this->Summary)."', 			description = '".mysql_real_escape_string($this->Description)."', 			date = '".$this->Date."', 
			image_thumbnail = '".mysql_real_escape_string($this->Image_thumbnail)."', 			image = '".mysql_real_escape_string($this->Image)."', 			show_homepage = '".$this->Show_homepage."', 			status = '".$this->Status."', 
			updatedate = '".$this->Updatedate."', 
			user_id = '".$this->User_id."',
			url_slug = '".clean_url_accents(mysql_real_escape_string($this->Url_slug))."'
			where news_id = '".mysql_real_escape_string($this->News_id)."'";
			$this->__mysql->Exec_query($sqlStr);

			//insert into tbl_news_category_link
			if(count($this->Category_array_id) > 0)
			{
				//delete the previous records
				$sqlStr = "delete from tbl_news_category_link where news_id = ".$this->News_id;
				mysql_query($sqlStr) or die("cannot execute query $sqlStr ". mysql_error());

				foreach($this->Category_array_id as $category_id)
				{
					$sqlStr = "insert into tbl_news_category_link
						(news_id, category_id) 
						values
						(".$this->News_id.", ".$category_id.")
						";
					mysql_query($sqlStr) or die("cannot execute query $sqlStr ". mysql_error());
				}

				$sqlStr = "optimize table tbl_news, tbl_news_category_link";
					$this->__mysql->Exec_query($sqlStr);
			}
	}

	public function Quick_update($column = null, $value = null)
	{
		$sqlStr = "update ".self::DBTable." set
			".$column." = '".mysql_real_escape_string($value)."',
			 updatedate = '".$this->Updatedate."' 
			where news_id = '".mysql_real_escape_string($this->News_id)."'";
			$this->__mysql->Exec_query($sqlStr);
	}

	public function List_all($limit = null, $offset = null)
	{
		$sqlStr = "select n.*, u.firstname, u.lastname
			from ".self::DBTable." n
			left join tbl_users u on n.user_id = u.user_id
			where n.is_deleted = 0 
			order by n.date desc";
		if(isset($limit) && isset($offset)){
			$sqlStr .= " limit $offset, $limit";
		}
		//echo $sqlStr;
		$recordset = $this->__mysql->Exec_query($sqlStr);
		return $data_array = $this->__mysql->Get_data_array($recordset);
	}

	public function List_frontend_news($limit = null, $offset = null, $category_slug = null, $is_insideamv = 0)
	{
		$category_id = 0;
		$sqlStr_where_cat = "";
		$cat_str = null;

		//stupid way to do it maybe but if the param "is_insideamv" == 1, load in the categories for that else load in the general categories for news IF category slug is empty
		//prepare default categories
		$cat_array = $this->__nc->List_frontend_categories($is_insideamv);
		foreach($cat_array as $cat)
		{
			$cat_str .= $cat['category_id'].',';
		}
		$cat_str = substr($cat_str, 0, -1);
		$sqlStr_where_cat = " and ncl.category_id in (".$cat_str.")";
		

		if(!is_null($category_slug))
		{
			//load in the cat_id for this slug
			$category_returned = $this->__nc->Select_by("url_slug", $category_slug);
			if($category_returned)
			{
				$category_id = $this->__nc->Category_id;
				$sqlStr_where_cat = " and ncl.category_id = ".$category_id."";
			}
		}
	
		$sqlStr = "select distinct(n.news_id), n.*
			from ".self::DBTable." n
			inner join ".self::DBTable."_category_link ncl on ncl.news_id = n.news_id 
			where 
			n.is_deleted = 0 and 
			n.status = 1  
			".$sqlStr_where_cat."
			order by n.date desc";
		if(isset($limit) && isset($offset)){
			$sqlStr .= " limit $offset, $limit";
		}
		//echo $sqlStr;
		$recordset = $this->__mysql->Exec_query($sqlStr);
		return $data_array = $this->__mysql->Get_data_array($recordset);
	}

	public function List_all_selectlist()
	{
		$sqlStr = "select news_id, title
			from ".self::DBTable."
			where is_deleted = 0 
			order by title";
		//echo $sqlStr;
		$recordset = $this->__mysql->Exec_query($sqlStr);
		return $data_array = $this->__mysql->Get_data_array($recordset);
	}

	public function Homepage_news()
	{
		$sqlStr = "select news_id, title, date
			from ".self::DBTable."
			where is_deleted = 0 and 
			show_homepage = 1 
			order by date desc";
		//echo $sqlStr;
		$recordset = $this->__mysql->Exec_query($sqlStr);
		return $data_array = $this->__mysql->Get_data_array($recordset);
	}

	public function Delete($news_id = 0)
	{
		$sqlStr = "update ".self::DBTable." set is_deleted = 1 where news_id = '".mysql_real_escape_string($news_id)."'";
			$this->__mysql->Exec_query($sqlStr);

		//delete the category from the tbl_news_category_link
		$sqlStr = "delete from tbl_news_category_link where news_id = '".mysql_real_escape_string($news_id)."'";
			$this->__mysql->Exec_query($sqlStr);

		$sqlStr = "optimize table ".self::DBTable.", tbl_news_category_link";
			$this->__mysql->Exec_query($sqlStr);
	}

	public function Optimize_table()
	{
		$sqlStr = "optimize table ".self::DBTable."";
			$this->__mysql->Exec_query($sqlStr);
	}

	public function Select($news_id = 0)
	{
		$sqlStr = "select * from ".self::DBTable." where news_id = '".mysql_real_escape_string($news_id)."'";
		$recordset = $this->__mysql->Exec_query($sqlStr);

		if($this->__mysql->Get_num_rows($recordset) > 0)
		{
			$result = $this->__mysql->Get_data_assoc($recordset);
				$this->News_id = $result['news_id'];				$this->News_md5 = $result['news_md5'];				$this->Panel_size = $result['panel_size'];				$this->Title = $result['title'];
				$this->Summary = $result['summary'];				$this->Description = $result['description'];				$this->Date = $result['date'];
				$this->Image_thumbnail = $result['image_thumbnail'];				$this->Image = $result['image'];				$this->Meta_title = $result['meta_title'];				$this->Meta_description = $result['meta_description'];				$this->Meta_keywords = $result['meta_keywords'];				$this->Show_homepage = $result['show_homepage'];
				$this->User_id = $result['user_id'];				$this->Status = $result['status'];				$this->Is_deleted = $result['is_deleted'];				$this->Createdate = $result['createdate'];				$this->Updatedate = $result['updatedate'];
				$this->Url_slug = $result['url_slug'];
				$this->Bitly_url = $result['bitly_url'];
			return true;
		}
		else
		{
			return false;
		}
	}

	public function Select_by($fieldname = null, $value = null)
	{
		if(!is_null($fieldname))
		{
			$sqlStr = "select * from ".self::DBTable." where ".$fieldname." = '".mysql_real_escape_string($value)."'";
			$recordset = $this->__mysql->Exec_query($sqlStr);

			if($this->__mysql->Get_num_rows($recordset) > 0)
			{
				$result = $this->__mysql->Get_data_assoc($recordset);
				$this->News_id = $result['news_id'];				$this->News_md5 = $result['news_md5'];				$this->Panel_size = $result['panel_size'];				$this->Title = $result['title'];
				$this->Summary = $result['summary'];				$this->Description = $result['description'];				$this->Date = $result['date'];
				$this->Image_thumbnail = $result['image_thumbnail'];				$this->Image = $result['image'];				$this->Meta_title = $result['meta_title'];				$this->Meta_description = $result['meta_description'];				$this->Meta_keywords = $result['meta_keywords'];				$this->Show_homepage = $result['show_homepage'];
				$this->User_id = $result['user_id'];				$this->Status = $result['status'];				$this->Is_deleted = $result['is_deleted'];				$this->Createdate = $result['createdate'];				$this->Updatedate = $result['updatedate'];
				$this->Url_slug = $result['url_slug'];
				$this->Bitly_url = $result['bitly_url'];
				return true;
			}
			else
			{
				return false;
			}
		}
	}

	public function Get_categories_assigned_to_news_id($news_id = 0)
	{
		$sqlStr = "select category_id
			from tbl_news_category_link
			where news_id = ".$news_id." 
			order by category_id asc";
		//echo $sqlStr;
		$recordset = $this->__mysql->Exec_query($sqlStr);
		return $data_array = $this->__mysql->Get_data_array($recordset);
	}
}
?>